/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package core;

import binding.carts.CartType;
import binding.carts.Order;
import binding.comments.CommentType;
import binding.comments.Comments;
import binding.orderdetails.OrderDetailType;
import binding.orderdetails.OrderDetails;
import binding.orders.OrderType;
import binding.orders.Orders;
import binding.products.ProductType;
import binding.products.Products;
import binding.userdetails.UserDetailType;
import binding.userdetails.UserDetails;
import binding.users.UserType;
import binding.users.Users;
import java.io.IOException;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.GregorianCalendar;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.xml.datatype.DatatypeConfigurationException;
import javax.xml.datatype.DatatypeFactory;

public class DBEntities {

    public static UserType checkLogin(String username, String password) throws SQLException {
        UserType ut = new UserType();
        String sql = "select u.isActive, u.Username, u.Id, r.Roles from dbo.User_Roles r join dbo.Users u on r.Id=u.RoleId where Username=? and Password = ?";
        Connection con = null;
        ResultSet rs = null;
        PreparedStatement pre = null;
        try {
            con = DBAccess.getConnection();
            pre = con.prepareStatement(sql);
            pre.setString(1, username);
            pre.setString(2, password);
            rs = pre.executeQuery();
            if (rs.next()) {
                ut.setRoleName(rs.getString("Roles"));
                ut.setId(BigInteger.valueOf(rs.getInt("Id")));
                ut.setUsername(rs.getString("Username"));
                ut.setIsActive(rs.getBoolean("isActive"));
            } else {
                return null;
            }
        } catch (Exception ex) {
            Logger.getLogger(DBEntities.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (con != null) {
                con.close();
            }
            if (pre != null) {
                pre.close();
            }
            if (rs != null) {
                rs.close();
            }
        }
        return ut;
    }

    public static boolean createAccount(String firstName, String lastName, String email,
            String province, String district, String address, String phoneNum, String username,
            String password) throws SQLException {
        boolean isDone = false;
        String sqlUd = "insert into User_Details (Name, Lastname, Email, "
                + "Province, District, Address, PhoneNum) "
                + "values(?,?,?,?,?,?,?)";
        String sqlU = "insert into Users (UserDetailId, Username, Password, "
                + "RoleId, isActive) values (?, ?, ?, ?, ?)";
        PreparedStatement pre = null;
        ResultSet rs = null;
        Connection con = null;
        try {
            con = DBAccess.getConnection();
            pre = con.prepareStatement(sqlUd, Statement.RETURN_GENERATED_KEYS);
            pre.setString(1, firstName);
            pre.setString(2, lastName);
            pre.setString(3, email);
            pre.setString(4, province);
            pre.setString(5, district);
            pre.setString(6, address);
            pre.setString(7, phoneNum);
            if (pre.executeUpdate() > 0) {
                rs = pre.getGeneratedKeys();
                if (rs.next()) {
                    pre = con.prepareStatement(sqlU);
                    pre.setInt(1, rs.getInt(1));
                    pre.setString(2, username);
                    pre.setString(3, password);
                    pre.setInt(4, 2);
                    pre.setBoolean(5, true);
                    if (pre.executeUpdate() > 0) {
                        isDone = true;
                    }
                }
            }
        } catch (Exception ex) {
            Logger.getLogger(DBEntities.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (con != null) {
                con.close();
            }
            if (pre != null) {
                pre.close();
            }
            if (rs != null) {
                rs.close();
            }
        }
        return isDone;
    }

    public static boolean checkUsername(String username) throws SQLException {
        String sql = "select * from Users where Username= ?";
        PreparedStatement pre = null;
        ResultSet rs = null;
        Connection con = null;
        try {
            con = DBAccess.getConnection();
            pre = con.prepareStatement(sql);
            pre.setString(1, username);
            rs = pre.executeQuery();
            if (rs.next()) {
                return true;
            }
        } catch (Exception ex) {
            Logger.getLogger(DBEntities.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (con != null) {
                con.close();
            }
            if (pre != null) {
                pre.close();
            }
            if (rs != null) {
                rs.close();
            }
        }
        return false;
    }

    public static Users getListUser() throws SQLException {
        String sql = "select *from Users";
        PreparedStatement pre = null;
        ResultSet rs = null;
        Connection con = null;
        try {
            con = DBAccess.getConnection();
            pre = con.prepareStatement(sql);
            rs = pre.executeQuery();
            Users listUser = new Users();
            while (rs.next()) {
                BigInteger id = BigInteger.valueOf(rs.getInt("Id"));
                BigInteger userDetailId = BigInteger.valueOf(rs.getInt("UserDetailId"));
                String username = rs.getString("Username");
                String password = rs.getString("Password");
                BigInteger roleId = BigInteger.valueOf(rs.getInt("RoleId"));
                Boolean isActive = rs.getBoolean("isActive");

                UserType user = new UserType();
                user.setId(id);
                user.setUserDetailId(userDetailId);
                user.setUsername(username);
                user.setRoleId(roleId);
                user.setIsActive(isActive);
                user.setRoleName("");

                listUser.getUser().add(user);

            }
            return listUser;
        } catch (Exception ex) {
            Logger.getLogger(DBEntities.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (con != null) {
                con.close();
            }
            if (pre != null) {
                pre.close();
            }
            if (rs != null) {
                rs.close();
            }
        }
        return null;
    }

    public static boolean saveUser(UserType user) throws IOException, SQLException {
        Connection con = null;
        try {
            String sql = "update Users set UserDetailId=?, Username=?,RoleId=?,IsActive=? where Id=?";
            con = DBAccess.getConnection();
            PreparedStatement pre = con.prepareStatement(sql);
            BigInteger bUserDetailId = user.getUserDetailId();
            if (bUserDetailId == null) {
                pre.setNull(1, Types.INTEGER);
            } else {
                pre.setInt(1, bUserDetailId.intValue());
            }

            pre.setString(2, user.getUsername());
            pre.setInt(3, user.getRoleId().intValue());
            pre.setBoolean(4, user.isIsActive());
            pre.setInt(5, user.getId().intValue());

            return pre.executeUpdate() > 0;
        } catch (Exception ex) {
            Logger.getLogger(DBEntities.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (con != null) {
                con.close();
            }
        }
        return false;
    }

    public static UserDetails getListUserDetail() throws SQLException {
        String sql = "select * from User_Details";
        PreparedStatement pre = null;
        ResultSet rs = null;
        Connection con = null;
        try {
            con = DBAccess.getConnection();
            pre = con.prepareStatement(sql);
            ;
            rs = pre.executeQuery();
            UserDetails listUserDetails = new UserDetails();
            while (rs.next()) {
                UserDetailType ud = new UserDetailType();

                ud.setId(BigInteger.valueOf(rs.getInt("Id")));
                ud.setName(rs.getString("Name"));
                ud.setLastname(rs.getString("Lastname"));
                ud.setEmail(rs.getString("Email"));
                ud.setProvince(rs.getString("Province"));
                ud.setDistrict(rs.getString("District"));
                ud.setAddress(rs.getString("Address"));
                ud.setPhoneNum(rs.getString("PhoneNum"));

                listUserDetails.getUserDetail().add(ud);

            }
            return listUserDetails;
        } catch (Exception ex) {
            Logger.getLogger(DBEntities.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (con != null) {
                con.close();
            }
            if (pre != null) {
                pre.close();
            }
            if (rs != null) {
                rs.close();
            }
        }
        return null;
    }

    public static Orders getOrders() throws SQLException, DatatypeConfigurationException {
        String sql = "select * from Product_Order";
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        Orders orders = new Orders();
        try {
            con = DBAccess.getConnection();
            st = con.createStatement();
            rs = st.executeQuery(sql);
            while (rs.next()) {
                //Pull data from DB to return OrderType
                OrderType orderType = new OrderType();
                orderType.setId(BigInteger.valueOf(rs.getInt("Id")));
                orderType.setUserId(BigInteger.valueOf(rs.getInt("UserId")));
                orderType.setPaymentMethod(rs.getString("PaymentMethod"));
                orderType.setDeliveryTime(rs.getString("DeliveryTime"));
                orderType.setTotalPriceAll(BigDecimal.valueOf(rs.getDouble("TotalPriceAll")));
                orderType.setNotes(rs.getString("Notes"));
                String status = "";
                if (rs.getString("Status") != null) {
                    status = rs.getString("Status");
                }
                orderType.setStatus(status);
                GregorianCalendar cldDate = new GregorianCalendar();
                cldDate.setTime(rs.getDate("Date"));
                orderType.setDate(DatatypeFactory.newInstance().newXMLGregorianCalendar(cldDate));

                orderType.setIsActive(rs.getBoolean("isActive"));

                //add to list of orders
                orders.getOrder().add(orderType);

            }
            return orders;
        } catch (Exception ex) {
            Logger.getLogger(DBEntities.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (st != null) {
                st.close();
            }
            if (con != null) {
                con.close();
            }
        }
        return null;
    }

    public static OrderDetails getOrderDetails() throws SQLException, DatatypeConfigurationException {
        String sql = "select * from Product_OrderDetails";
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        OrderDetails orders = new OrderDetails();
        try {
            con = DBAccess.getConnection();
            st = con.createStatement();
            rs = st.executeQuery(sql);
            while (rs.next()) {
                //Pull data from DB to return OrderType
                OrderDetailType orderType = new OrderDetailType();
                orderType.setId(BigInteger.valueOf(rs.getInt("Id")));
                orderType.setOrderId(BigInteger.valueOf(rs.getInt("OrderId")));
                orderType.setProductId(BigInteger.valueOf(rs.getInt("ProductId")));
                orderType.setQuantity(BigInteger.valueOf(rs.getInt("Quantity")));
                orderType.setPrice(BigDecimal.valueOf(rs.getDouble("Price")));
                orderType.setTotalPrice(BigDecimal.valueOf(rs.getDouble("TotalPrice")));

                orders.getOrderDetail().add(orderType);

            }
            return orders;
        } catch (Exception ex) {
            Logger.getLogger(DBEntities.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (st != null) {
                st.close();
            }
            if (con != null) {
                con.close();
            }
        }
        return null;
    }

    public static Products getProducts() throws SQLException, DatatypeConfigurationException {
        String sql = "select Id,Name,ImageUrl,Description,Quantity,Price,isActive from Products";
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        Products products = new Products();
        try {
            con = DBAccess.getConnection();
            st = con.createStatement();
            rs = st.executeQuery(sql);
            while (rs.next()) {
                ProductType pt = new ProductType();
                pt.setId(BigInteger.valueOf(rs.getInt("Id")));
                pt.setName(rs.getString("Name"));
                pt.setImageURL(rs.getString("ImageUrl"));
                pt.setDescription(rs.getString("Description"));
                pt.setQuantity(BigInteger.valueOf(rs.getInt("Quantity")));
                pt.setPrice(rs.getBigDecimal("Price"));
                pt.setIsActive(rs.getBoolean("isActive"));
                products.getProduct().add(pt);
            }
            return products;
        } catch (Exception ex) {
            Logger.getLogger(DBEntities.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (st != null) {
                st.close();
            }
            if (con != null) {
                con.close();
            }
        }
        return null;
    }

    public static boolean saveProduct(ProductType pro) throws IOException, SQLException {
        Connection con = null;
        try {
            String sql = "update Products set Name=?,ImageUrl=?,Description=?,Price=?,isActive=? where Id=?";
            con = DBAccess.getConnection();
            PreparedStatement pre = con.prepareStatement(sql);
            pre.setString(1, pro.getName());
            pre.setString(2, pro.getImageURL());
            pre.setString(3, pro.getDescription());
            pre.setBigDecimal(4, pro.getPrice());
            pre.setBoolean(5, pro.isIsActive());
            pre.setInt(6, pro.getId().intValue());
            return pre.executeUpdate() > 0;
        } catch (Exception ex) {
            Logger.getLogger(DBEntities.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (con != null) {
                con.close();
            }
        }
        return false;
    }

    public static boolean addProduct(ProductType pro) throws IOException, SQLException {
        Connection con = null;
        try {
            String sql = "insert into Products (Name,ImageUrl,Description,Price,isActive) values(?,?,?,?,?)";
            con = DBAccess.getConnection();
            PreparedStatement pre = con.prepareStatement(sql);
            pre.setString(1, pro.getName());
            pre.setString(2, pro.getImageURL());
            pre.setString(3, pro.getDescription());
            pre.setBigDecimal(4, pro.getPrice());
            pre.setBoolean(5, pro.isIsActive());
            return pre.executeUpdate() > 0;
        } catch (Exception ex) {
            Logger.getLogger(DBEntities.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (con != null) {
                con.close();
            }
        }
        return false;
    }

    public static boolean updateComment(CommentType obj) throws SQLException, SQLException, SQLException, SQLException {
        Connection con = null;
        try {
            String sql = "update Product_Comment set isActive=? where Id=?";
            con = DBAccess.getConnection();
            PreparedStatement pre = con.prepareStatement(sql);
            pre.setBoolean(1, obj.isIsActive());
            pre.setInt(2, obj.getId().intValue());
            return pre.executeUpdate() > 0;
        } catch (Exception ex) {
            Logger.getLogger(DBEntities.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (con != null) {
                con.close();
            }
        }
        return false;
    }

    public static boolean deleteComment(String id) throws SQLException, SQLException, SQLException, SQLException {
        Connection con = null;
        try {
            String sql = "delete from Product_Comment where Id=?";
            con = DBAccess.getConnection();
            PreparedStatement pre = con.prepareStatement(sql);
            pre.setInt(1, Integer.parseInt(id));
            return pre.executeUpdate() > 0;
        } catch (Exception ex) {
            Logger.getLogger(DBEntities.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (con != null) {
                con.close();
            }
        }
        return false;
    }


    public static Comments getListComments() throws SQLException, DatatypeConfigurationException {
        String sql = "select * from Product_Comment";

        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        Comments comments = new Comments();
        try {
            con = DBAccess.getConnection();
            st = con.createStatement();
            rs = st.executeQuery(sql);
            while (rs.next()) {
                CommentType commentType = new CommentType();

                commentType.setId(BigInteger.valueOf(rs.getInt("Id")));
                commentType.setUserId(BigInteger.valueOf(rs.getInt("UserId")));
                commentType.setProductId(BigInteger.valueOf(rs.getInt("ProductId")));
                commentType.setComment(rs.getString("Comment"));
                commentType.setRating(BigInteger.valueOf(rs.getInt("Rating")));
                commentType.setIsActive(rs.getBoolean("isActive"));

                GregorianCalendar cldDate = new GregorianCalendar();
                cldDate.setTime(rs.getDate("Timestamp"));
                commentType.setTimestamp(DatatypeFactory.newInstance().newXMLGregorianCalendar(cldDate));

                comments.getComment().add(commentType);
            }
            return comments;
        } catch (Exception ex) {
            Logger.getLogger(DBEntities.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (st != null) {
                st.close();
            }
            if (con != null) {
                con.close();
            }
        }
        return null;
    }

    public static boolean saveOrder(OrderType order) throws IOException, SQLException {
        Connection con = null;
        try {
            String sql = "update Product_Order set Status=?,isActive=? where Id=?";
            con = DBAccess.getConnection();
            PreparedStatement pre = con.prepareStatement(sql);
            pre.setString(1, order.getStatus());
            pre.setBoolean(2, order.isIsActive());
            pre.setInt(3, order.getId().intValue());
            return pre.executeUpdate() > 0;
        } catch (Exception ex) {
            Logger.getLogger(DBEntities.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (con != null) {
                con.close();
            }
        }
        return false;
    }

    public static boolean saveNewComment(CommentType com) {

        Connection con = null;
        String sql = "insert into Product_Comment (UserId,ProductId,Comment,Rating,Timestamp,isActive) values (?,?,?,?,?,?)";
        try {
            con = DBAccess.getConnection();
            PreparedStatement pre = con.prepareStatement(sql);
            pre.setInt(1, com.getUserId().intValue());
            pre.setInt(2, com.getProductId().intValue());
            pre.setString(3, com.getComment());
            pre.setInt(4, com.getRating().intValue());
            pre.setDate(5, new Date(com.getTimestamp().toGregorianCalendar().getTime().getTime()));
            pre.setBoolean(6, com.isIsActive());
            return pre.executeUpdate() > 0;

        } catch (Exception ex) {
            Logger.getLogger(DBEntities.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    Logger.getLogger(DBEntities.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return false;
    }

    public static UserDetailType getUserDetailById(String uid) throws SQLException {
        String sql = "select * from dbo.User_Details r join dbo.Users u on r.Id=u.UserDetailId where Id=?";
        UserDetailType udt = new UserDetailType();
        PreparedStatement pre = null;
        ResultSet rs = null;
        Connection con = null;
        try {
            con = DBAccess.getConnection();
            pre = con.prepareStatement(sql);
            pre.setString(1, uid);
            rs = pre.executeQuery();

            if (rs.next()) {
                udt.setName(rs.getString("Name"));
                udt.setLastname(rs.getString("Lastname"));
                udt.setEmail(rs.getString("Email"));
                udt.setProvince(rs.getString("Province"));
                udt.setDistrict(rs.getString("District"));
                udt.setAddress(rs.getString("Address"));
                udt.setPhoneNum(rs.getString("PhoneNum"));
            } else {
                return null;
            }
        } catch (Exception ex) {
            Logger.getLogger(DBEntities.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (con != null) {
                con.close();
            }
            if (pre != null) {
                pre.close();
            }
            if (rs != null) {
                rs.close();
            }
        }
        return udt;
    }

    public static int saveNewOrder(Order com) throws SQLException {
        Connection con = null;
        try {
            String sql = "insert into Product_Order (UserId,PaymentMethod,Date,DeliveryTime,Notes,Status,TotalPriceAll,isActive) values (?,?,?,?,?,?,?,?)";
            con = DBAccess.getConnection();
            PreparedStatement pre = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            pre.setInt(1, com.getUserId().intValue());
            pre.setString(2, "Cash");
            GregorianCalendar gDate = new GregorianCalendar();
            gDate.setTime(new java.util.Date());
            pre.setDate(3, new Date(DatatypeFactory.newInstance().newXMLGregorianCalendar(gDate).toGregorianCalendar().getTime().getTime()));
            pre.setString(4, null);
            pre.setString(5, null);
            pre.setString(6, null);
            int totalPrice = 0;
            for (CartType Cart : com.getCart()) {
                if (Cart.getTotalPrice() != null) {
                    totalPrice += Cart.getTotalPrice().intValue();
                }
            }
            pre.setInt(7, totalPrice);
            pre.setBoolean(8, true);

            pre.executeUpdate();
            ResultSet rs = pre.getGeneratedKeys();
            int orderkey = 0;
            if (rs.next()) {
                orderkey = rs.getInt(1);
            }
            String sqlOrderItem = "insert into Product_OrderDetails (OrderId,ProductId,Quantity,Price,TotalPrice) values (?,?,?,?,?)";
            for (CartType cart : com.getCart()) {
                PreparedStatement pr = con.prepareStatement(sqlOrderItem);
                pr.setInt(1, orderkey);
                pr.setInt(2, cart.getProductId().intValue());
                pr.setInt(3, cart.getQuantity().intValue());
                pr.setDouble(4, cart.getPrice().doubleValue());
                pr.setDouble(5, cart.getTotalPrice().doubleValue());
                boolean inner = pr.executeUpdate() > 0;
                if (!inner) {
                    return 0;
                }
            }
            return orderkey;
        } catch (DatatypeConfigurationException ex) {
            Logger.getLogger(DBEntities.class.getName()).log(Level.SEVERE, null, ex);
        } catch (Exception ex) {
            Logger.getLogger(DBEntities.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (con != null) {
                con.close();
            }
        }
        return 0;
    }
}
